﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using KZ.Exception;
using KZ.Lottery.Resources;

namespace KZ.Lottery.DAL
{
    public class DataAccessHelper
    {
        #region Private Variables

        private static string _ConnStr;
        private static SqlConnection _SqlConnection;
        private static SqlCommand _SqlCmd;
        private static SqlTransaction _SqlTransaction;

        #endregion Private Variables

        #region Private Method

        private static void OpenConnection()
        {
            try
            {
                if (_SqlConnection == null)
                {
                    _SqlConnection = new SqlConnection(_ConnStr);
                }
                else if (_SqlConnection.State == ConnectionState.Closed)
                {
                    _SqlConnection.Open();
                }
            }
            catch (InvalidOperationException)
            {
                throw new DataAccessException(KZ_Message.BLM_ERR_CONNECT_SERVER);
            }
            catch (SqlException)
            {
                throw new DataAccessException(KZ_Message.BLM_ERR_CONNECT_SERVER);
            }
        }

        private static void CloseConnection()
        {
            try
            {
                if (_SqlConnection.State != ConnectionState.Closed &&
                    _SqlConnection != null)
                {
                    _SqlConnection.Close();
                }
            }
            catch (SqlException)
            {
                throw new DataAccessException(KZ_Message.BLM_ERR_CONNECT_SERVER);
            }
        }

        #endregion Private Method

        #region Public Members

        public static SqlConnection SqlConnect
        {
            get { return _SqlConnection; }
        }

        public static SqlCommand SqlCmd
        {
            get { return _SqlCmd; }
        }

        public static SqlTransaction SqlTrans
        {
            get { return _SqlTransaction; }
        }

        #endregion Public Members

        #region Public Methods

        public static SqlConnection GetConnection()
        {
            return _SqlConnection;
        }

        public static void RefeshConnection()
        {
            _ConnStr = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
            _SqlConnection = new SqlConnection(_ConnStr);
            _SqlCmd = new SqlCommand(String.Empty, _SqlConnection);
        }

        public static void BeginTransaction(IsolationLevel iso)
        {
            try
            {
                if (_SqlConnection.State == ConnectionState.Open)
                {
                    _SqlTransaction = _SqlConnection.BeginTransaction(iso);
                }
                else if (_SqlConnection != null)
                {
                    _SqlConnection.Open();
                    _SqlTransaction = _SqlConnection.BeginTransaction(iso);
                }
                else
                {
                    throw new System.Exception();
                }
            }
            catch (System.Exception ex)
            {
                Log.LogEx("BeginTransaction", ex);
                throw new DataAccessException(KZ_Message.BLM_ERR_GENERAL);
            }
        }

        public static void CommitTransaction()
        {
            try
            {
                if (_SqlConnection.State == ConnectionState.Open)
                {
                    _SqlTransaction.Commit();
                }
                else
                {
                    throw new System.Exception();
                }
            }
            catch (System.Exception ex)
            {
                Log.LogEx("CommitTransaction", ex);
                throw new DataAccessException(KZ_Message.BLM_ERR_GENERAL);
            }
            finally
            {
                _SqlTransaction.Dispose();
                CloseConnection();
            }
        }

        public static void RollbackTransaction()
        {
            try
            {
                if (_SqlConnection.State == ConnectionState.Open)
                {
                    _SqlTransaction.Rollback();
                }
                else
                {
                    throw new System.Exception();
                }
            }
            catch (System.Exception ex)
            {
                Log.LogEx("RollbackTransaction", ex);
                throw new DataAccessException(KZ_Message.BLM_ERR_GENERAL);
            }
            finally
            {
                _SqlTransaction.Dispose();
                CloseConnection();
            }
        }

        public static int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            try
            {
                OpenConnection();

                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                return _SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                ClearCommand();
                CloseConnection();
            }
        }

        public static int ExecuteNonQueryNotClearCommand(CommandType cmdType, string cmdText)
        {
            try
            {
                OpenConnection();

                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                return _SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }

        public static int ExecuteNonQueryWithTransaction(CommandType cmdType, string cmdText)
        {
            try
            {
                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                _SqlCmd.Transaction = _SqlTransaction;
                return _SqlCmd.ExecuteNonQuery();
            }
            finally
            {
                ClearCommand();
            }
        }

        public static SqlDataReader ExecuteQuery(CommandType cmdType, string cmdText)
        {
            try
            {
                OpenConnection();

                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;

                return _SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            finally
            {
                ClearCommand();
            }
        }

        public static SqlDataReader ExecuteQueryWithTransaction(CommandType cmdType, string cmdText)
        {
            try
            {
                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                _SqlCmd.Transaction = _SqlTransaction;

                return _SqlCmd.ExecuteReader();
            }
            finally
            {
                ClearCommand();
            }
        }

        public static object ExecuteScalar(CommandType cmdType, string cmdText)
        {
            try
            {
                OpenConnection();

                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                return _SqlCmd.ExecuteScalar();
            }
            finally
            {
                ClearCommand();
                CloseConnection();
            }
        }

        public static object ExecuteScalarWithTransaction(CommandType cmdType, string cmdText)
        {
            try
            {
                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                _SqlCmd.Transaction = _SqlTransaction;

                return _SqlCmd.ExecuteScalar();
            }
            finally
            {
                ClearCommand();
            }
        }

        public static int ExecuteBatchInsertWithTransaction(CommandType cmdType, string cmdText, DataTable dt)
        {
            try
            {
                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                _SqlCmd.Transaction = _SqlTransaction;
                _SqlCmd.UpdatedRowSource = UpdateRowSource.None;

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.InsertCommand = _SqlCmd;
                sqlDataAdapter.UpdateBatchSize = 10;

                return sqlDataAdapter.Update(dt);
            }
            finally
            {
                ClearCommand();
            }
        }

        public static int ExecuteBatchUpdateWithTransaction(CommandType cmdType, string cmdText, DataTable dt)
        {
            try
            {
                _SqlCmd.CommandType = cmdType;
                _SqlCmd.CommandText = cmdText;
                _SqlCmd.Transaction = _SqlTransaction;

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand("Select * from [dbo].[RuleDetails]");
                sqlDataAdapter.UpdateCommand = _SqlCmd;
                sqlDataAdapter.UpdateBatchSize = 10;

                return sqlDataAdapter.Update(dt);
            }
            finally
            {
                ClearCommand();
            }
        }

        public static void ClearCommand()
        {
            _SqlCmd.Parameters.Clear();
        }

        #endregion Public Methods
    }
}